package com.example.exceldemo.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.example.exceldemo.entity.*;
import com.example.exceldemo.exception.ServiceException;
import com.example.exceldemo.service.EasyExcelService;
import com.example.exceldemo.utils.easy.ExcelUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.util.*;

/**
 * @Author xioahuang
 * @Date 2024/7/14 10:45
 * @Version 1.0
 * @description
 **/
@Slf4j
@Service
public class EasyExcelServiceImpl implements EasyExcelService {
    private static final String NORMAN_FILE_PATH = "template/fillexecl/list.xlsx";
    private static final String TEMPLATE_FILE_PATH = "template/fillexecl/company.xlsx";
    private static final String CHARSET_UTF8 = "utf-8";
    private static final String TEMPLATE_PATH = "template/fillexecl/company2.xlsx";


    @Override
    public void export() {
        List<Person> peopleList = personList();
        ExcelUtils.export(peopleList, Person.class, "人员信息", "人员信息");
    }

    @Override
    public void exportHead() {
        List<PersonHead> personHeadList = personHeadList();
        ExcelUtils.export(personHeadList, PersonHead.class, "人员信息", "人员信息");

    }

    /**
     * 导出多个Sheet到Excel文件
     */
    @Override
    public void exportSheet() {
        try {
            HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding(CHARSET_UTF8);
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("测试普通多sheet导出", CHARSET_UTF8) + ".xlsx");
            try (ServletOutputStream out = response.getOutputStream()) {
                try (ExcelWriter excelWriter = EasyExcel.write(out)
                        // 注册日期转换器（已注释）
                        //.registerConverter(new LocalDateStringConverter())
                        .registerWriteHandler(ExcelUtils.formatExcel()).build()) {
                    // 获取人员列表和设备列表数据
                    List<Person> peopleList = personList();
                    List<IotSensorDataExportListItem> result = result();

                    // 创建两个Sheet，分别用于人员和设备数据
                    WriteSheet sheet1 = EasyExcel.writerSheet(0, "人员").head(Person.class).build();
                    WriteSheet sheet2 = EasyExcel.writerSheet(1, "设备").head(IotSensorDataExportListItem.class).build();

                    // 将数据写入到对应的Sheet中
                    excelWriter.write(peopleList, sheet1);
                    excelWriter.write(result, sheet2);
                }
            } catch (IOException e) {
                log.error("导出Excel异常{}", e.getMessage());
                throw new ServiceException("导出Excel异常：" + e.getMessage());
            }

        } catch (IOException e) {
            log.error("导出Excel IO异常: {}", e.getMessage(), e);
            throw new ServiceException("导出Excel IO异常：" + e.getMessage());
        } catch (ServiceException e) {
            log.error("服务异常: {}", e.getMessage(), e);
            throw e;
        } catch (Exception e) {
            log.error("导出异常: {}", e.getMessage(), e);
            throw new ServiceException("导出异常：" + e.getMessage());
        }
    }


    /**
     * 设置Excel导出的方法
     * 该方法用于导出一个包含书籍信息的Excel文件
     */
    @Override
    public void excelSetExport() {
        HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
        ServletOutputStream out = null;
        try {
            // 定义Excel表格的表头信息
            String[] head = {"书名", "价格", "类型"};
            // 获取书籍数据列表
            List<BookExcel> bookList = bookData();

            // 创建一个新的Excel工作簿
            XSSFWorkbook workbook = new XSSFWorkbook();
            // 在工作簿中创建一个新的工作表
            XSSFSheet sheet = workbook.createSheet();

            // 设置工作表中列的宽度
            sheet.setColumnWidth(0, 50 * 256); // 设置第一列的宽度为50个字符的宽度
            sheet.setColumnWidth(1, 50 * 256); // 设置第二列的宽度为50个字符的宽度
            sheet.setColumnWidth(2, 50 * 256); // 设置第三列的宽度为50个字符的宽度

            // 创建标题合并单元格
            CellRangeAddress region1 = new CellRangeAddress(0, 0, 0, 2);
            sheet.addMergedRegion(region1);
            Row title = sheet.createRow(0);
            title.setHeight((short) 1000);
            Cell titleCell = title.createCell(0);
            // 创建标题的样式
            XSSFCellStyle headStyle = ExcelUtils.createCellStyle(workbook, (short) 22, true, true, false, false);
            titleCell.setCellStyle(headStyle);
            titleCell.setCellValue("员工信息");

            // 创建内容单元格样式
            XSSFCellStyle style = ExcelUtils.createCellStyle(workbook, (short) 14, false, true, false, false);
            // 创建表头样式
            XSSFCellStyle headStyles = ExcelUtils.createCellStyle(workbook, (short) 18, false, true, true, false);

            // 创建表头行
            Row row = sheet.createRow(1);
            // 遍历表头信息并设置到单元格中
            for (int i = 0; i < head.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellType(CellType.STRING);
                cell.setCellValue(head[i]);
                cell.setCellStyle(headStyles);
            }

            // 检查书籍列表是否为空，如果为空则直接导出空的Excel文件
            int size = bookList.size();
            if (size == 0) {
                response.setCharacterEncoding(CHARSET_UTF8);
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
                response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("测试自定义导出", "UTF-8") + ".xlsx");
                response.flushBuffer();
                workbook.write(response.getOutputStream());
            }

            // 遍历书籍列表并填充到Excel表格中
            for (int i = 0; i < size; i++) {
                Row row1 = sheet.createRow(i + 2);
                if (i >= bookList.size()) {
                    break;
                }
                System.out.println("bookList.size() = " + bookList.size());
                BookExcel book = bookList.get(i);

                Cell cell = row1.createCell(0);
                cell.setCellStyle(style);
                cell.setCellValue(book.getName());

                Cell cell1 = row1.createCell(1);
                cell1.setCellStyle(style);
                cell1.setCellValue(book.getPrice());

                Cell cell2 = row1.createCell(2);
                cell2.setCellStyle(style);
                cell2.setCellValue(book.getType());
            }

            // 设置响应的字符编码和内容类型，准备将Excel文件作为响应内容发送给客户端
            response.setCharacterEncoding(CHARSET_UTF8);
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("测试自定义导出", "UTF-8") + ".xlsx");
            response.flushBuffer();
            out = response.getOutputStream();
            workbook.write(out);
        } catch (IOException e) {
            throw new ServiceException("导出excel异常" + e.getMessage());
        } finally {
            if (out != null) {
                try {
                    out.flush();
                    out.close();
                } catch (IOException e) {
                    throw new ServiceException("关闭输出流异常" + e.getMessage());
                }
            }
        }
    }


    @Override
    public void excelTemplate() {
        InputStream templateFileName = null;
        try {
            templateFileName = new ClassPathResource(TEMPLATE_FILE_PATH).getInputStream();
        } catch (IOException e) {
            throw new ServiceException("读取模板异常" + e.getMessage());
        }
        ServletOutputStream out = null;
        try {
            HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
            response.setCharacterEncoding(CHARSET_UTF8);
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("测试模板导出", "UTF-8") + ".xlsx");
            out = response.getOutputStream();
            try (ExcelWriter excelWriter = EasyExcel.write(out).withTemplate(templateFileName).build()) {
                // 初始化写入的Sheet配置
                WriteSheet writeSheet = EasyExcel.writerSheet().build();
                // 创建一个Map对象，用于存储待填充到Excel中的数据
                Map<String, Object> map = new HashMap<>();
                // 构建填充配置，设置是否强制换行
                FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
                //填充单个数据
                map.put("date", "2023-07");
                map.put("structureSum", 1);
                map.put("underSum", 2);
                map.put("concreteSum", 3);
                // 填充列表数据，设置“是”强置换行，因为列表下面有备注，所以需要强制换行，不然会覆盖
                excelWriter.fill(dateApply(), fillConfig, writeSheet);
                // 填充Map中的数据
                excelWriter.fill(map, writeSheet);
            }

        } catch (IOException e) {
            throw new ServiceException("导出异常：" + e.getMessage());
        } finally {
            if (templateFileName != null) {
                try {
                    templateFileName.close();
                } catch (IOException e) {
                    throw new ServiceException("关闭流异常：" + e.getMessage());
                }
            }
        }
    }


    /**
     * 多sheet模版导出
     */
    @Override
    public void exportTemplateSheet() {
        try (InputStream templateFileName = new ClassPathResource(TEMPLATE_PATH).getInputStream()) {
            HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
            response.setCharacterEncoding(CHARSET_UTF8);
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + encodeFilename("测试模板导出") + ".xlsx");
            try (ServletOutputStream out = response.getOutputStream()) {
                try (ExcelWriter excelWriter = EasyExcel.write(out).withTemplate(templateFileName).build()) {
                    ////多个sheet时名字要与模版中的sheet名字保持一样
                    WriteSheet writeSheet = EasyExcel.writerSheet("sheet1").build();
                    WriteSheet userSheet = EasyExcel.writerSheet("sheet2").build();
                    Map<String, Object> map = new HashMap<>();
                    FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
                    map.put("date", "2023-07");
                    map.put("structureSum", 1);
                    map.put("underSum", 2);
                    map.put("concreteSum", 3);
                    excelWriter.fill(dateApply(), fillConfig, writeSheet);
                    excelWriter.fill(map, writeSheet);
                    excelWriter.fill(userData(), userSheet);
                }
            } catch (IOException e) {
                log.error("导出异常：" + e.getMessage(), e);
                throw new ServiceException("导出异常");
            }
        } catch (IOException e) {
            log.error("读取模板异常：" + e.getMessage(), e);
            throw new ServiceException("读取模板异常");
        }
    }

    private String encodeFilename(String filename) {
        try {
            return URLEncoder.encode(filename, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            log.error("编码转换异常：" + e.getMessage(), e);
            throw new ServiceException("编码转换异常");
        }
    }

    @Override
    public void commonTemplateExport() {
        InputStream templateFileName = null;
        try {
            templateFileName = new ClassPathResource(NORMAN_FILE_PATH).getInputStream();
            System.out.println(templateFileName);
        } catch (IOException e) {
            throw new ServiceException("读取模板异常" + e.getMessage());
        }
        ServletOutputStream out = null;
        try {
            HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
            response.setCharacterEncoding(CHARSET_UTF8);
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("测试模板导出", "UTF-8") + ".xlsx");
            out = response.getOutputStream();
            EasyExcel.write(out).withTemplate(templateFileName).sheet().doFill(data());
        } catch (IOException e) {
            throw new ServiceException("导出异常：" + e.getMessage());
        } finally {
            if (templateFileName != null) {
                try {
                    templateFileName.close();
                } catch (IOException e) {
                    throw new ServiceException("关闭流异常：" + e.getMessage());
                }
            }
        }
    }

    @Override
    public void excelMergeExport() {
        ExcelUtils.exportMergeExcel("测试自定义合并单元格导出.xlsx", result(), IotSensorDataExportListItem.class, new int[]{1, 2}, 1);
    }

    @Override
    public void excelManyExport() {
        HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
        try {
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("库存", "UTF-8") + ".xlsx");
            OutputStream outputStream = response.getOutputStream();
            List<Person> personList = personBatch();
            // 计算总页数
            Integer total = personList.size(); // 总记录数
            EasyExcel.write(outputStream, Person.class).sheet("库存").doWrite(() -> {
                int pageSize = 10000;
                int pageNum = 1;
                List<Person> allItems = new ArrayList<>();
                int pages = total % pageSize == 0 ? total / pageSize : total / pageSize + 1;
                int fromIndex;
                while (true) {
                    if (personList.size() == 0 || pageNum > pages)
                        break;
                    // fromIndex开始索引，toIndex结束索引
                    if (pages == 0) {
                        fromIndex = 0;
                    } else
                        fromIndex = pageNum <= 0 ? 0 : (pageNum > pages ? (pages - 1) * pageSize : (pageNum - 1) * pageSize);
                    int toIndex = pageNum != pages ? fromIndex + pageSize : personList.size();
                    allItems.addAll(personList.subList(fromIndex, toIndex));

                    pageNum++;
                }
                return allItems;
            });

        } catch (Exception ex) {
            try {

                ex.printStackTrace();

                response.setCharacterEncoding("utf-8");

                response.sendError(500, "系统内部错误," + ex.getMessage());

                response.getWriter().write("系统内部错误," + ex.getMessage());

            } catch (IOException e) {

                e.printStackTrace();

            }

        }
    }

    private List<IotSensorDataExportListItem> result() {
        List<IotSensorDataExportListItem> list = ListUtils.newArrayList();
        IotSensorDataExportListItem fillData1 = new IotSensorDataExportListItem();
        fillData1.setIndex("1");
        fillData1.setDeviceName("白天");
        fillData1.setAreaName("广州");
        fillData1.setDesc("最大值");
        fillData1.setNum(100);
        IotSensorDataExportListItem fillData2 = new IotSensorDataExportListItem();
        fillData2.setIndex("2");
        fillData2.setDeviceName("白天");
        fillData2.setAreaName("广州");
        fillData2.setDesc("最小值");
        fillData2.setNum(10);
        IotSensorDataExportListItem fillData3 = new IotSensorDataExportListItem();
        fillData3.setIndex("3");
        fillData3.setDeviceName("夜晚");
        fillData3.setAreaName("深圳");
        fillData3.setDesc("最大值");
        fillData3.setNum(1000);
        IotSensorDataExportListItem fillData4 = new IotSensorDataExportListItem();
        fillData4.setIndex("4");
        fillData4.setDeviceName("夜晚");
        fillData4.setAreaName("深圳");
        fillData4.setDesc("最小值");
        fillData4.setNum(100);
        list.add(fillData1);
        list.add(fillData2);
        list.add(fillData3);
        list.add(fillData4);
        return list;

    }

    private List<FillData> data() {
        List<FillData> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            FillData fillData = new FillData();
            list.add(fillData);
            fillData.setName("张三");
            fillData.setNumber(5.2);
            fillData.setDate(new Date());
        }
        return list;
    }

    //获取应用情况中的列表数据，注意需要和模版字段相对应
    private List<ConcreteApplyDataExport> dateApply() {
        List<ConcreteApplyDataExport> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            ConcreteApplyDataExport fillData = new ConcreteApplyDataExport();
            fillData.setName("张三");
            fillData.setLine(i + 1);
            fillData.setStructureCount(1);
            fillData.setConcreteNum(i);
            fillData.setUnderNum(i);
            list.add(fillData);
        }
        return list;
    }

    //获取用户列表信息
    private List<UserTest> userData() {
        List<UserTest> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            UserTest fillData = new UserTest();
            fillData.setName("张" + i);
            fillData.setLine(i + 1);
            fillData.setJob("程序员");
            fillData.setWorkAge(i);
            fillData.setAge(18 + i);
            list.add(fillData);
        }
        return list;
    }

    private List<BookExcel> bookData() {
        ArrayList<BookExcel> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            BookExcel bookExcel = new BookExcel();
            bookExcel.setName("book" + i);
            bookExcel.setPrice(i);
            bookExcel.setType("类型" + i);
            list.add(bookExcel);
        }
        return list;
    }

    private List<Person> personList() {
        List<Person> list = new ArrayList<>();
        for (int i = 0; i < 20; i++) {
            Person fillData = new Person();
            fillData.setName("张" + i);
            fillData.setLine(i + 1);
            fillData.setJob("程序员");
            fillData.setWorkAge(i);
            fillData.setAge(18 + i);
            fillData.setDate(LocalDateTime.now());
            fillData.setDoubleData(BigDecimal.valueOf(0.56769));
            fillData.setNum(88.946);

            list.add(fillData);
        }
        return list;
    }

    private List<PersonHead> personHeadList() {
        List<PersonHead> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            PersonHead fillData = new PersonHead();
            fillData.setName("张" + i);
            fillData.setLine(i + 1);
            fillData.setJob("程序员");
            fillData.setWorkAge(i);
            fillData.setAge(18 + i);
            list.add(fillData);
        }
        return list;
    }

    private List<Person> personBatch() {
        List<Person> list = new ArrayList<>();
        for (int i = 1; i < 1000000; i++) {
            Person fillData = new Person();
            fillData.setName("张" + i);
            fillData.setLine(i + 1);
            fillData.setJob("程序员");
            fillData.setWorkAge(i);
            fillData.setAge(18 + i);
            fillData.setDate(LocalDateTime.now());
            fillData.setDoubleData(BigDecimal.valueOf(0.56769));
            fillData.setNum(88.946);

            list.add(fillData);
        }
        return list;
    }
}
